package back_database;

import java.sql.*;
import java.util.*;

public class Select_Means {
	
	// select_shangpin  select_shangpinbianhao  select_merchandisename
	public static final String SELECT_SHANGPIN
				="select * from Merchandise";  // 所有商品信息
	
	public static final String SELECT_SHANGPINBIANHAO
				= "select * from Merchandise where MerchandiseNumber like ?";
	
	public static final String SELECT_MERCHANDISENAME
	= "select * from Merchandise where MerchandiseName like ?";
	
	// select_shangpintype  selectStock
	public static final String SELECT_SHANGPINTYPE
				= "select * from Merchandise where SortNumber like ?" ;

	public static final String  SELECTSTOCK
				= "select ID, stock.MerchandiseNumber, MerchandiseName, number "
						+ "from stock,merchandise "
						+ "where stock.MerchandiseNumber = merchandise.MerchandiseNumber";  // 仓库信息表3个字段
	
	// Select_RepertoryId
	public static final String  SELECT_REPERTORYID
	= "select ID, stock.MerchandiseNumber, MerchandiseName, number "
			+ "from stock,merchandise "
			+ "where stock.MerchandiseNumber = merchandise.MerchandiseNumber "
			+ "and stock.MerchandiseNumber like ?";  // 根据查询仓库商品ID， 返回仓库信息表3个字段
	// 2021年5月17日 记录： sql语句 多表查询时， 重复字段前一定要加表名 如stock.MerchandiseNumber

	public static final String SELECT_USER
	="select * from usermanage";
	//select_User select_salesinfo
	public static final String SELECT_SALESINFO
	="select MerchandiseNumber  , MerchandiseName ,  amount , RetailPrice , salesdate , salesmoney  from salestatistic order by salesdate desc";
	//"select MerchandiseNumber as '商品编号' , MerchandiseName as '商品名称',  amount as '商品数量', RetailPrice as '单价', salesdate as 日期, salesmoney as '小计'  from salestatistic order by salesdate desc";
	// "select MerchandiseNumber  , MerchandiseName ,  amount , PurchasePrice , salesdate , salesmoney  from salestatistic order by salesdate desc";
	//"select MerchandiseNumber as 商品编号 , MerchandiseName as 商品名称,  amount as 商品数量, RetailPrice as 单价, salesdate as 日期, salesmoney as 小计  from salestatistic order by salesdate desc";
	//"select MerchandiseNumber as '商品编号' , MerchandiseName as '商品名称',  amount as '商品数量', RetailPrice as '单价', salesdate as 日期, salesmoney as '小计'  from salestatistic order by salesdate desc";
	//select_cashbalance
	public static final String SELECT_CASHBALANCE
	="select * from cashbalance order by date desc ";

	public static final String SELECT_REFUND = "select * from refund order by date desc ";

	public static final String SELECT_PURCHASE = "select date, purchase.MerchandiseNumber , merchandise.MerchandiseName, purchase.amount  from purchase, merchandise where purchase.MerchandiseNumber = merchandise.MerchandiseNumber order by date ";
	
	public static  Vector Select_shangpin() // 商品查询
	{
		Connection conn = null;	
		PreparedStatement pmst = null;
		ResultSet rs = null;
		Vector vector2 = new Vector();
		conn = DBAccess.getConnection();
		
		try {
			pmst = conn.prepareStatement(SELECT_SHANGPIN);  // 预编译语句
			rs = pmst.executeQuery();  // 执行查询
			
			while(rs.next()) {  // 对查询结果集进行遍历
				Vector vector1 = new Vector(); // 创建集合
				vector1.add(rs.getString(1));   // 商品ID
				vector1.add(rs.getString(2));   // 商品编号
				vector1.add(rs.getString(3));   // 条形码
				vector1.add(rs.getString(4));   // 商品类别
				vector1.add(rs.getString(5));   // 商品名称
				vector1.add(rs.getString(6));   // 商品价格
				vector1.add(rs.getString(7));   // 商品单位
				vector1.add(rs.getString(8));   // 商品进货价
				  
			// 获取需要保存的信息保存到集合中  ， 在界面中显示这些信息
				vector2.add(vector1);  // 将商品添加到商品集合中
			}
			
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			
		} finally {
			// 文档中省略了  关闭资源的代码
			shutDown(conn);
		}
		return vector2;
	}  //end of  Select_shangpin
	
	// 断开连接
	public static void shutDown(Connection connection)
	{
		try {
			// 断开数据库连接
			connection.close();
		}
		catch( SQLException sqlex)
		{
			System.out.println("Unable to disconnect");
			sqlex.printStackTrace();
		}
	}
	
	// 参数商品名称， 查到它整条信息返回。
	public static  Vector Select_MerchandiseName(String name) // 商品名称查询
	{
		Connection conn = null;
		PreparedStatement pmst = null;
		ResultSet rs = null;
		Vector vector2 = new Vector();
		conn = DBAccess.getConnection();
		
		try {
			pmst = conn.prepareStatement(SELECT_MERCHANDISENAME );  // 预编译语句
			pmst.setString(1, "%" + name + "%" );  // 设置商品名称
			rs = pmst.executeQuery();  // 执行查询
			
			while(rs.next()) {  // 对查询结果集进行遍历
				Vector vector1 = new Vector(); // 创建集合
				vector1.add(rs.getString(1));   // 商品ID
				vector1.add(rs.getString(2));   // 商品编号
				vector1.add(rs.getString(3));   // 条形码
				vector1.add(rs.getString(4));   // 商品类别
				vector1.add(rs.getString(5));   // 商品名称
				vector1.add(rs.getString(6));   // 商品价格
				vector1.add(rs.getString(7));   // 商品单位
				vector1.add(rs.getString(8));   // 商品进货价
				  
			// 获取需要保存的信息保存到集合中  ， 在界面中显示这些信息
				vector2.add(vector1);  // 将商品添加到商品集合中
			}
			
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			
		} finally {
			shutDown(conn);
		}
		return vector2;
	}  //end of  Select_MerchandiseName
	
	
	// 参数商品编号， 返回该条记录信息
	public static  Vector Select_sort3(String number) // 商品编号查询
	{
		Connection conn = null;
		PreparedStatement pmst = null;
		ResultSet rs = null;
		Vector vector2 = new Vector();
		conn = DBAccess.getConnection();
		
		try {
			pmst = conn.prepareStatement(SELECT_SHANGPINBIANHAO );  // 预编译语句
			pmst.setString(1, "%" + number + "%" );  // 设置商品名称
			rs = pmst.executeQuery();  // 执行查询
			
			while(rs.next()) {  // 对查询结果集进行遍历
				Vector vector1 = new Vector(); // 创建集合
				vector1.add(rs.getString(1));   // 商品ID
				vector1.add(rs.getString(2));   // 商品编号
				vector1.add(rs.getString(3));   // 条形码
				vector1.add(rs.getString(4));   // 商品类别
				vector1.add(rs.getString(5));   // 商品名称
				vector1.add(rs.getString(6));   // 商品价格
				vector1.add(rs.getString(7));   // 商品单位
				vector1.add(rs.getString(8));   // 商品进货价
				  
			// 获取需要保存的信息保存到集合中  ， 在界面中显示这些信息
				vector2.add(vector1);  // 将商品添加到商品集合中
			}
			
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			
		} finally {
			shutDown(conn);
		}
		return vector2;
	}  //end of  Select_sort3
	
	// 商品类别查询 
	public static  Vector Select_sortType(String number)   
	{
		Connection conn = null;
		PreparedStatement pmst = null;
		ResultSet rs = null;
		Vector vector2 = new Vector();
		conn = DBAccess.getConnection();
		
		try {
			pmst = conn.prepareStatement(SELECT_SHANGPINTYPE );  // 预编译语句
			pmst.setString(1, "%" + number + "%" );  // 设置商品名称
			rs = pmst.executeQuery();  // 执行查询
			
			while(rs.next()) {  // 对查询结果集进行遍历
				Vector vector1 = new Vector(); // 创建集合
				vector1.add(rs.getString(1));   // 商品ID
				vector1.add(rs.getString(2));   // 商品编号
				vector1.add(rs.getString(3));   // 条形码
				vector1.add(rs.getString(4));   // 商品类别
				vector1.add(rs.getString(5));   // 商品名称
				vector1.add(rs.getString(6));   // 商品价格
				vector1.add(rs.getString(7));   // 商品单位
				vector1.add(rs.getString(8));   // 商品进货价
				  
			// 获取需要保存的信息保存到集合中  ， 在界面中显示这些信息
				vector2.add(vector1);  // 将商品添加到商品集合中
			}
			
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			
		} finally {
			shutDown(conn);
		}
		return vector2;
	}  //end of  Select_sortType
	
	
	 // 查询所有库存
	public static Vector Select_All_repertory() 
	{
		Vector vector2 = new Vector(); // 将vecotr1 装入形成一条数据
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pmst = null;
		
		try {
			conn = DBAccess.getConnection();
			pmst = conn.prepareStatement(SELECTSTOCK);
			rs = pmst.executeQuery();
		
			while(rs.next())
			{
				Vector vector1 = new Vector(); // 记录查询的数据
				
				vector1.add(rs.getString(1));  // 库存编号
				vector1.add(rs.getString(2));  // 商品编码
				vector1.add(rs.getString(3));  // 库存名称
				vector1.add(rs.getString(4));  // 库存数量
				vector2.add(vector1);  // 添加到商品集合中
				
			}
		
		} catch (Exception ex)
		{
			ex.printStackTrace(); 
		} finally {
			shutDown(conn);
		}
			
		return vector2;
	}
	
	//根据商品编码查询库存
	public static Vector Select_RepertoryId(String Product_ID) { 
		Vector vector2 = new Vector(); //将vector1装入形成条数据
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pmst = null;
		try {
				conn = DBAccess.getConnection();
				pmst = conn.prepareStatement(SELECT_REPERTORYID); //select10
				pmst.setString(1, Product_ID);
				rs = pmst.executeQuery();
				while (rs.next()) {
					Vector vector1 = new Vector(); //记录查询的数据
					vector1.add(rs.getString(1));  // 库存编号
					vector1.add(rs.getString(2));  // 商品编码
					vector1.add(rs.getString(3));  // 库存名称
					vector1.add(rs.getString(4));  // 库存数量
					vector2.add(vector1);  // 添加到商品集合中
					 
				}
	
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			shutDown(conn);
		}
		return vector2;
	
	}
	
	// 查询用户资料
	public static Vector select_User() {
			Vector vector2 = new Vector(); // 将vecotr1 装入形成一条数据
			Connection conn = null;
			ResultSet rs = null;
			PreparedStatement pmst = null;
			
			try {
				conn = DBAccess.getConnection();
				pmst = conn.prepareStatement(SELECT_USER);
				rs = pmst.executeQuery();
			
				while(rs.next())
				{
					Vector vector1 = new Vector(); // 记录查询的数据
					vector1.add(rs.getString(1));  // CardId  
					vector1.add(rs.getString(2));  // UserName
					vector1.add(rs.getString(3));  // IDcard
					vector1.add(rs.getString(4));  // Password
					vector1.add(rs.getString(5));  // CardDate
					vector1.add(rs.getString(6));  // UserGrade
					vector1.add(rs.getString(7));  // agio
					
					vector2.add(vector1);  // 添加到商品集合中
					
				}
			
			} catch (Exception ex)
			{
				ex.printStackTrace(); 
			} finally {
				shutDown(conn);
			}
				
			return vector2;
		}
		
	/** 销售信息浏览
	 * 返回销售信息salestatistic表中的数据
	 * */
	public static  Vector Select_SalesInfo() // 销售信息浏览
	{
		Connection conn = null;
		PreparedStatement pmst = null;
		ResultSet rs = null;
		Vector vector2 = new Vector();
		conn = DBAccess.getConnection();
		
		try {
			pmst = conn.prepareStatement(SELECT_SALESINFO);  // 预编译语句
			rs = pmst.executeQuery();  // 执行查询
			
			while(rs.next()) {  // 对查询结果集进行遍历
				Vector vector1 = new Vector(); // 创建集合
				vector1.add(rs.getString(1));   // 商品编号
				vector1.add(rs.getString(2));   // 商品名称
				vector1.add(rs.getString(3));   // 销售数量
				vector1.add(rs.getString(4));   // 单价
				vector1.add(rs.getString(5));   // 日期
				vector1.add(rs.getString(6));   // 小计
				  
			// 获取需要保存的信息保存到集合中  ， 在界面中显示这些信息
				vector2.add(vector1);  // 将商品添加到商品集合中
			}
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			
		} finally {
			// 文档中省略了  关闭资源的代码
			shutDown(conn);
		}
		return vector2;
	}  //end of  select_salesinfo
	
	
	// select_cashbalance
	/** 超市收支信息浏览
	 * 返回超市收支信息cashbalance表中的数据
	 * */
	public static  Vector Select_CashBalance() // 销售信息浏览
	{
		Connection conn = null;
		PreparedStatement pmst = null;
		ResultSet rs = null;
		Vector vector2 = new Vector();
		conn = DBAccess.getConnection();
		
		try {
			pmst = conn.prepareStatement(SELECT_CASHBALANCE);  // 预编译语句
			rs = pmst.executeQuery();  // 执行查询
			
			while(rs.next()) {  // 对查询结果集进行遍历
				Vector vector1 = new Vector(); // 创建集合
				vector1.add(rs.getString(1));   // 日期
				vector1.add(rs.getString(2));   // 收入
				vector1.add(rs.getString(3));   // 支出
				  
			// 获取需要保存的信息保存到集合中  ， 在界面中显示这些信息
				vector2.add(vector1);  // 将商品添加到商品集合中
			}
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			
		} finally {
			// 文档中省略了  关闭资源的代码
			shutDown(conn);
		}
		return vector2;
	}  //end of  select_cashbalance
	
	
	// select_UserNumber;
		/** 用户信息密码修改
		 * 返回用户表中usermanage的输入账号的密码
		 * */
		public static  String Select_UserCode(String UserIDcard) // 会员信息
		{
			Connection conn = null;
			PreparedStatement pmst = null;
			ResultSet rs = null;
			Vector vector2 = new Vector();
			conn = DBAccess.getConnection();
			String result = null;
			try {
				String select_UserNumber = "select Password from usermanage where IDcard = " + UserIDcard;
//				System.out.println(select_UserNumber );
				
				pmst = conn.prepareStatement(select_UserNumber );  // 预编译语句
				rs = pmst.executeQuery();  // 执行查询
				
				
				if(! rs.next()) { return "0";}  // !rs.isBeforeFirst()
				else {
					result = rs.getString(1);
//					System.out.println(rs.getString(1));
				}
				
			} catch (SQLException ex) {
				ex.printStackTrace();
				
			} finally {
				// 文档中省略了  关闭资源的代码
				shutDown(conn);
			}
			return result ;
		}  //end of  select_cashbalance
		  
		
		// select_UserNumber;
		/** 对数据库中的用户密码修改操作
		 * usermanage的输入账号的密码
		 * */
		public static  void Select_UserCode(String UserIDcard, String password) // 销售信息浏览
		{
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			Vector vector2 = new Vector();
			conn = DBAccess.getConnection();
			String result = null;
			try {
				String sql = "update usermanage set  Password ="+ password +" where IDcard = " + UserIDcard;
				
				stmt = conn.createStatement();  // 预编译语句
				stmt.execute(sql);
				System.out.println("更新成功");
				
			} catch(SQLException  e) { 
				e.printStackTrace();
			}
			
			finally {
				// 文档中省略了  关闭资源的代码
				shutDown(conn);
			}
		}  //end of  select_cashbalance
		  	
		
		
		
		
		/** 超市退货信息浏览
		 * 返回超市收支信息refund表中的数据
		 * */
		public Vector selectRefund() {
			Vector vector2 = new Vector(); // 将vecotr1 装入形成一条数据
			Connection conn = null;
			ResultSet rs = null;
			PreparedStatement pmst = null;
			
			try {
				conn = DBAccess.getConnection();
				pmst = conn.prepareStatement(SELECT_REFUND);
				rs = pmst.executeQuery();
			
				while(rs.next())
				{
					Vector vector1 = new Vector(); // 记录查询的数据
					
					vector1.add(rs.getString(1));  // 日期
					vector1.add(rs.getString(2));  // 商品编码
					vector1.add(rs.getString(3));  // 商品名称
					vector1.add(rs.getString(4));  // 商品数量
					vector2.add(vector1);  // 添加到商品集合中
					
				}
			
			} catch (Exception ex)
			{
				ex.printStackTrace(); 
			} finally {
				shutDown(conn);
			}
				
			return vector2;
		}

		
		/** 超市进货信息浏览
		 * 返回超市收支信息purchase表中的数据
		 * 
		 * 此查询是连接查询select 通过商品编号 查询了商品表中的商品名称字段
		 * */
		public Vector selectPurchase() {
			Vector vector2 = new Vector(); // 将vecotr1 装入形成一条数据
			Connection conn = null;
			ResultSet rs = null;
			PreparedStatement pmst = null;
			
			try {
				conn = DBAccess.getConnection();
				pmst = conn.prepareStatement(SELECT_PURCHASE);
				rs = pmst.executeQuery();
			
				while(rs.next())
				{
					Vector vector1 = new Vector(); // 记录查询的数据
					
					vector1.add(rs.getString(1));  // 日期
					vector1.add(rs.getString(2));  // 商品编号
					vector1.add(rs.getString(3));  // 商品名称
					vector1.add(rs.getString(4));  // 商品数量
					vector2.add(vector1);  // 添加到商品集合中
					
				}
			
			} catch (Exception ex)
			{
				ex.printStackTrace(); 
			} finally {
				shutDown(conn);
			}
				
			return vector2;
		}
	
}


// Page 24 

// Page 25